package com.seaboxdata.scheduler;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.mongodb.client.result.DeleteResult;
import com.mongodb.client.result.UpdateResult;
import com.seaboxdata.commons.id.IdGenerator;
import com.seaboxdata.entity.ColumnInfo;
import com.seaboxdata.entity.MdsObject;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.mongodb.core.MongoOperations;
import org.springframework.data.mongodb.core.MongoTemplate;
import org.springframework.data.mongodb.core.query.Criteria;
import org.springframework.data.mongodb.core.query.Query;
import org.springframework.data.mongodb.core.query.Update;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Component
@Configuration
@Slf4j
public class CsvBuilderJob {

    @Resource
    private MongoOperations mongoOperations;

    @Autowired
    private MongoTemplate mongoTemplate;

    @Autowired
    private IdGenerator idGenerator;

    @Value("${csv.build.dbs}")
    private String dbsStr;

    @Scheduled(cron = "${csv.build}")
    public void doCollect() throws Exception {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        String format = "yyyy-MMdd HH:mm:ss";
        SimpleDateFormat sdf = new SimpleDateFormat(format);
        //连接orientdb
        try {
            Class.forName("com.orientechnologies.orient.jdbc.OrientJdbcDriver");
            conn = DriverManager.getConnection("jdbc:orient:remote:172.21.17.51:30006/mds_server_deploy", "root", "root");
        } catch (Exception e) {
            System.out.println("connect orientdb failed");
            e.printStackTrace();
        }
        Query updateQuery = new Query(Criteria.where("db_type").is("MYSQL"));
        UpdateResult updateResult = mongoTemplate.updateMulti(updateQuery, Update.update("schema", "mysql"), "mds_object");
        List<MdsObject> mdsObjects = mongoTemplate.findAll(MdsObject.class);


        //将schema为空的替换成mysql schema

        //invalid hexadecimal representation of an ObjectId: [337971509500973058]

        //mongoOperations.updateMulti();
        for (MdsObject mdsObject : mdsObjects) {
            List<ColumnInfo> columnInfos = new ArrayList<>();
            List<String> mdsColumns = mdsObject.getMds_columns();
            for (String mdsColumn : mdsColumns) {
                JSONObject jsonObject = JSON.parseObject(mdsColumn);
                ColumnInfo columnInfo = new ColumnInfo();
                columnInfo.setColumnName((String) jsonObject.get("name"));
                Object id = jsonObject.get("_id");
                for (Map.Entry<String, Object> stringObjectEntry : ((JSONObject) id).entrySet()) {
                    columnInfo.setColumnId((String) stringObjectEntry.getValue());
                }
                columnInfo.setDesc((String) jsonObject.get("desc"));
                columnInfos.add(columnInfo);
            }
            mdsObject.setColumnInfos(columnInfos);
        }

        Map<String, MdsObject> landingTables = new HashMap<>();
        Map<String, MdsObject> subjectTables = new HashMap<>();
        Map<String, MdsObject> historyTables = new HashMap<>();
        Map<String, MdsObject> mppSubjectTables = new HashMap<>();

        int landingaCount = 0;
        int historyCount = 0;
        int mppSubjectCount = 0;
        int subjectCount = 0;
        Map<String,MdsObject> deleteMdsObjects = new HashMap<>();

        for (MdsObject mdsObject : mdsObjects) {
            if ("landing_db".equals(mdsObject.getData_base_name())) {
                landingaCount++;
                landingTables.put(mdsObject.getName(), mdsObject);
            } else if ("history_db".equals(mdsObject.getData_base_name())) {
                historyCount++;
                historyTables.put(mdsObject.getName(), mdsObject);
            } else if ("subject_db".equals(mdsObject.getData_base_name()) && "MYSQL".equals(mdsObject.getDb_type())) {
                subjectCount++;
                subjectTables.put(mdsObject.getName(), mdsObject);
            } else if ("subject_db".equals(mdsObject.getData_base_name()) && !"MYSQL".equals(mdsObject.getDb_type())) {
                mppSubjectCount++;
                if(mppSubjectTables.get(mdsObject.getName()) != null){
                    if(mppSubjectTables.get(mdsObject.getName()).getModify_tm().compareTo(mdsObject.getModify_tm()) < 0){

                        Query query=new Query(Criteria.where("_id").is(mppSubjectTables.get(mdsObject.getName()).getId()));
                        DeleteResult remove = mongoTemplate.remove(query, MdsObject.class);

                       /* Bson deleteBson = Filters.eq("name",mppSubjectTables.get(mdsObject.getName()).getName());
                        mongoOperations.getCollection("mds_object").findOneAndDelete(deleteBson);*/
                        deleteMdsObjects.put(mdsObject.getName(),mppSubjectTables.get(mdsObject.getName()));
                        mppSubjectTables.put(mdsObject.getName(),mdsObject);
                    }else{
                        //Bson deleteBson = Filters.eq(mdsObject.getId());
                        //mongoOperations.getCollection("mds_object").deleteOne(deleteBson);
                        //mongoOperations.remove(mdsObject);
                        deleteMdsObjects.put(mdsObject.getName(),mdsObject);
                    }
                }else{
                    mppSubjectTables.put(mdsObject.getName(), mdsObject);
                }
            } else {
                System.out.println(mdsObject.getData_base_name() + "." + mdsObject.getName());
            }
        }


        List<String> bloodInsertResults = new ArrayList<>();
        List<String> lostedResults = new ArrayList<>();

        //组装表关系和字段关系

        for (Map.Entry<String, MdsObject> stringMdsObjectEntry : landingTables.entrySet()) {
            String key = stringMdsObjectEntry.getKey();
            String historyKey = key.replaceFirst("l", "h");
            String subjectKey = key.replaceFirst("l", "s");
            //历史跟landing的关系
            MdsObject landingMdsObject = stringMdsObjectEntry.getValue();
            MdsObject hisMdsObject = historyTables.get(historyKey);
            MdsObject subjectMdsObject = subjectTables.get(subjectKey);
            MdsObject mppSubjectMdsObject = mppSubjectTables.get(subjectKey);

            if (null != hisMdsObject) {
                handle(landingMdsObject, hisMdsObject, conn,bloodInsertResults);
            } else {
                lostedResults.add(landingMdsObject.getData_base_name() + "." + landingMdsObject.getName());
                System.out.println("history_db not have this table:" + landingMdsObject.getData_base_name() + "." + landingMdsObject.getName());
            }

            //subject跟landing的关系
            if (null != subjectMdsObject) {
                handle(landingMdsObject, subjectMdsObject, conn,bloodInsertResults);
            } else {
                lostedResults.add(landingMdsObject.getData_base_name() + "." + landingMdsObject.getName());
                System.out.println("subject_db not have this table:" + landingMdsObject.getData_base_name() + "." + landingMdsObject.getName());
            }

            //subject与mpp主题库的关系
            if (subjectMdsObject != null && mppSubjectMdsObject != null) {
                if(deleteMdsObjects.get(mppSubjectMdsObject.getName()) != null){
                    deleteHandle(subjectMdsObject,deleteMdsObjects.get(mppSubjectMdsObject.getName()),conn);
                }
                handle(subjectMdsObject, mppSubjectMdsObject, conn,bloodInsertResults);
            } else {
                lostedResults.add(landingMdsObject.getData_base_name() + "." + landingMdsObject.getName());
                System.out.println("subject_db or mpp subject_db not have this table:" + landingMdsObject.getData_base_name() + "." + landingMdsObject.getName());
            }

        }

        //将数据写入到文件
        System.out.println(landingaCount);
        System.out.println(historyCount);
        System.out.println(mppSubjectCount);
        System.out.println(subjectCount);
        System.out.println(bloodInsertResults);
        System.out.println(lostedResults);
        System.out.println("blood excute end");

    }

    private void deleteHandle(MdsObject subjectMdsObject, MdsObject mppMdsObject, Connection conn) throws Exception {
        String deleteMdsObjectRelation = "delete EDGE MdsObjectRelation where srcId='%s' and tgtId='%s'";
        String insetMdsObjectRelation = "CREATE EDGE MdsObjectRelation FROM ( select from MdsObject where id='%s' limit 1) TO ( select from MdsObject where id='%s' limit 1) SET id = '%s', srcId='%s', tgtId='%s',srcName='%s', srcDesc='%s', tgtName='%s', tgtDesc='%s', etlId='%s'";
        String deleteMdsColumnRelation = "delete EDGE MdsColumnRelation where srcId='%s' and tgtId='%s'";
        String insertMdsColumnRelation = "CREATE EDGE MdsColumnRelation FROM ( select from MdsColumn where id='%s' limit 1) TO ( select from MdsColumn where id='%s' limit 1) SET id = '%s', srcId='%s', tgtId='%s',  srcName='%s', srcDesc='%s', tgtName='%s', tgtDesc='%s',  srcMdsId='%s', srcMdsName='%s', tgtMdsId='%s', tgtMdsName='%s', etlId='%s'";
        String deleteObjectRelationSql = String.format(deleteMdsObjectRelation, subjectMdsObject.getId(), mppMdsObject.getId());

        //删除原有的表关系,新建血缘分析
        PreparedStatement deletePt = conn.prepareStatement(deleteObjectRelationSql);
        deletePt.execute();
    }


    public void handle(MdsObject srcMdsObject, MdsObject trgMdsObject, Connection conn, List<String> results) throws SQLException {
        String deleteMdsObjectRelation = "delete EDGE MdsObjectRelation where srcId='%s' and tgtId='%s'";
        String insetMdsObjectRelation = "CREATE EDGE MdsObjectRelation FROM ( select from MdsObject where id='%s' limit 1) TO ( select from MdsObject where id='%s' limit 1) SET id = '%s', srcId='%s', tgtId='%s',srcName='%s', srcDesc='%s', tgtName='%s', tgtDesc='%s', etlId='%s'";
        String deleteMdsColumnRelation = "delete EDGE MdsColumnRelation where srcId='%s' and tgtId='%s'";
        String insertMdsColumnRelation = "CREATE EDGE MdsColumnRelation FROM ( select from MdsColumn where id='%s' limit 1) TO ( select from MdsColumn where id='%s' limit 1) SET id = '%s', srcId='%s', tgtId='%s',  srcName='%s', srcDesc='%s', tgtName='%s', tgtDesc='%s',  srcMdsId='%s', srcMdsName='%s', tgtMdsId='%s', tgtMdsName='%s', etlId='%s'";
        String deleteObjectRelationSql = String.format(deleteMdsObjectRelation, srcMdsObject.getId(), trgMdsObject.getId());

        String insertObjectRelationSql = String.format(insetMdsObjectRelation, srcMdsObject.getId(), trgMdsObject.getId(), idGenerator.getId(),
                srcMdsObject.getId(), trgMdsObject.getId(), srcMdsObject.getName(), srcMdsObject.getChinese_name() == null ? null : srcMdsObject.getChinese_name().replaceAll("\n"," ").replaceAll("\r"," "), trgMdsObject.getName(),
                trgMdsObject.getChinese_name() == null ? null : trgMdsObject.getChinese_name().replaceAll("\n"," ").replaceAll("\r"," "), "342255582281076736");
        //删除原有的表关系,新建血缘分析
        PreparedStatement deletePt = conn.prepareStatement(deleteObjectRelationSql);
        deletePt.execute();
        try{
            PreparedStatement insertPt = conn.prepareStatement(insertObjectRelationSql);
            insertPt.execute();
        }catch(Exception e){
            e.printStackTrace();
            System.out.println(srcMdsObject.getData_base_name() + srcMdsObject.getName() + " to " + trgMdsObject.getData_base_name() + trgMdsObject.getName());
        }

        for (ColumnInfo trgColumnInfo : trgMdsObject.getColumnInfos()) {
            //处理字段血缘关系
            List<ColumnInfo> columnInfos = srcMdsObject.getColumnInfos();
            //获取landing字段的id
            ColumnInfo srcColumnInfo = null;
            for (ColumnInfo info : columnInfos) {
                if (info.getColumnName().equals(trgColumnInfo.getColumnName())) {
                    srcColumnInfo = info;
                }
            }
            if (srcColumnInfo != null) {
                String deleteMdsColumnRelationSql = String.format(deleteMdsColumnRelation, srcColumnInfo.getColumnId(), trgColumnInfo.getColumnId());
                String insertMdsColumnRelationSql = String.format(insertMdsColumnRelation, srcColumnInfo.getColumnId(),
                        trgColumnInfo.getColumnId(), idGenerator.getId(), srcColumnInfo.getColumnId(), trgColumnInfo.getColumnId(),
                        srcColumnInfo.getColumnName(), srcColumnInfo.getDesc() == null ? null : srcColumnInfo.getDesc().replaceAll("\n"," ").replaceAll("\r"," "), trgColumnInfo.getColumnName(),
                        trgColumnInfo.getDesc() == null ? null : trgColumnInfo.getDesc().replaceAll("\n"," ").replaceAll("\r"," "),
                        srcMdsObject.getId(), srcMdsObject.getName(), trgMdsObject.getId(), trgMdsObject.getName(), "342255582281076736");
                PreparedStatement deleteColumnPt = conn.prepareStatement(deleteMdsColumnRelationSql);
                deleteColumnPt.execute();
                try {
                    PreparedStatement insertColumnPt = conn.prepareStatement(insertMdsColumnRelationSql);
                    insertColumnPt.execute();
                } catch (Exception e) {
                    e.printStackTrace();
                    System.out.println(srcMdsObject.getData_base_name() + srcMdsObject.getName() + " " + trgMdsObject.getData_base_name() + trgMdsObject.getName() + insertMdsColumnRelationSql);
                }

            }

        }
        results.add(srcMdsObject.getData_base_name() + "." + srcMdsObject.getName() + " to " + trgMdsObject.getData_base_name() + "." + trgMdsObject.getName());
        System.out.println("write relation " + srcMdsObject.getData_base_name() + "." + srcMdsObject.getName() + " to " + trgMdsObject.getData_base_name() + "." + trgMdsObject.getName());
    }
}
